package cn.gzsxt.utils;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class DataUtil {
	// 按事务提交
	public static Integer transaction(String sql, String[] key) {
		int count = 0;
		int[] rows = null;
		Connection conn = null;
		PreparedStatement ps = null;
		conn = DBUtil.getConn();

		try {
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < key.length; i++) {
				ps.setObject(1, key[i]);
				ps.addBatch();
			}
			rows = ps.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				// 回滚事务
				conn.rollback();
			} catch (Exception e2) {
			}
		} finally {
			DBUtil.close(conn, ps, null);
			for (int j = 0; j < rows.length; j++) {
				count += rows[j];

			}
		}
		return count;
	}

	/**
	 * 通用查询 可以执行任何查询语句 并且将查询结果解析为指定类型的对象 保存在list中返回
	 * 
	 * @param sql
	 *            查询语句
	 * @param className
	 *            查询结果要封装成的对象的类型
	 * @return 查询结果
	 */
	public static <T> List<T> selectData(String sql, Class<T> t, Object... key) {
		// Statement stmt = DBUtil.getStmt();

		Connection conn = null;
		PreparedStatement ps = null;

		conn = DBUtil.getConn();

		ResultSet set = null;
		// 创建一个list 用来准备接收解析出来的对象
		List<T> list = new ArrayList<>();
		try {
			ps = conn.prepareStatement(sql);
			if (key.length != 0) {
				for (int i = 0; i < key.length; i++) {
					ps.setObject(i + 1, key[i]);
				}
			}
			// 执行sql语句 获取查询结果
			set = ps.executeQuery();
			// 获取查询结果中每一列的列名
			ResultSetMetaData md = set.getMetaData();
			// 获取查询结果有多少列
			int count = md.getColumnCount();
			// 创建一个数组用于存放列的名字
			String[] colName = new String[count];
			// 将查询结果的每一列的列名都存入数组中
			for (int i = 1; i <= count; i++) {
				colName[i - 1] = md.getColumnName(i);
			}

			// 获取类的反射
			Class<T> cls = t;
			// 通过反射获取类的所有属性
			Field[] fs = cls.getDeclaredFields();

			// 开始解析
			while (set.next()) {
				// 通过反射创建一个对象
				T obj = cls.newInstance();
				list.add(obj);
				// 循环获取属性
				for (Field field : fs) {
					// 判断属性的类型
					if ("int".equals(field.getType().getName())
							|| "java.lang.Integer".equals(field.getType().getName())) {
						// 寻找跟属性名同名的字段
						for (int i = 0; i < colName.length; i++) {
							String name = colName[i];
							// 如果字段名跟属性名同名,就用该字段的值给属性赋值
							if (name.toUpperCase().equals(field.getName().toUpperCase())) {
								field.setAccessible(true);
								field.set(obj, set.getInt(name));
							}
						}
					} else if ("java.lang.String".equals(field.getType().getName())) {
						for (int i = 0; i < colName.length; i++) {
							String name = colName[i];
							if (name.toUpperCase().equals(field.getName().toUpperCase())) {
								field.setAccessible(true);
								field.set(obj, set.getString(name));
							}
						}
					} else if ("java.sql.Date".equals(field.getType().getName())
							|| "java.util.Date".equals(field.getType().getName())) {
						for (int i = 0; i < colName.length; i++) {
							String name = colName[i];
							if (name.toUpperCase().equals(field.getName().toUpperCase())) {
								field.setAccessible(true);
								field.set(obj, set.getDate(name));
							}
						}
					}
				}
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, set);
		}

		return list;
	}

	/**
	 * 使用反射将任意对象插入到对应的表中
	 * 
	 * @param obj
	 *            所要插入的对象
	 * @return 返回插入结果
	 */
	public static <T> int insertData(T obj, Integer... key) {
		// insert into className values (?,?,?,?)
		// 获取反射
		@SuppressWarnings("unchecked")
		Class<T> cls = (Class<T>) obj.getClass();
		// 获取创建对象的类名,就是要插入数据的表的名字
		String tempName = cls.getSimpleName();
		String className = tempName.toLowerCase();
		
		
		// 获取所有的属性
		Field[] fs = cls.getDeclaredFields();

		Field[] fst = new Field[fs.length];
		int count = 0;
		for (int i = 0; i < fs.length; i++) {
			Field f = fs[i];
			f.setAccessible(true);
			Object value = null;
			try {
				value = f.get(obj);
			} catch (IllegalArgumentException | IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			if ("int".equals(f.getType().getName())) {
				if (0 != (int) value) {
					fst[count] = f;
					count++;
				}
			} else if ("java.lang.String".equals(f.getType().getName())) {
				if (null != (String) value) {
					fst[count] = f;
					count++;
				}
			} else if ("java.sql.Date".equals(f.getType().getName())
					|| "java.util.Date".equals(f.getType().getName())) {
				if (null != (Date) value) {
					fst[count] = f;
					count++;
				}
			} else if ("java.lang.Integer".equals(f.getType().getName())) {
				if (null != (Integer) value) {
					fst[count] = f;
					count++;
				}
			}
		}
		String sql = null;
		// 拼接sql语句
		if (key.length == 0) {
			sql = "insert into " + className + " (";
			for (int i = 0; i < count; i++) {
				if (i < count - 1) {
					sql = sql + fst[i].getName() + ",";
				} else {
					sql = sql + fst[i].getName() + ")values(";
				}
			}
			for (int i = 0; i < count; i++) {
				if (i < count - 1) {
					sql = sql + "?,";
				} else {
					sql = sql + "?)";
				}
			}
		} else {
			sql = "update " + className + " set ";
			for (int i = 0; i < count; i++) {
				if (i < count - 1) {
					sql = sql + fst[i].getName() + " = ? , ";
				} else {
					sql = sql + fst[i].getName() + " = ? where id = ?";
				}
			}
		}
		int j = 0;

		Connection conn = null;
		PreparedStatement ps = null;
		try {
			System.out.println(sql);
			// 用拼接好的sql语句 创建一个预处理块
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			// 向预处理块中填充参数
			for (int i = 0; i < count; i++) {
				ps.setObject(i + 1, fst[i].get(obj));
			}
			if (key.length != 0) {
				ps.setObject(count + 1, key[0]);
			}
			j = ps.executeUpdate();
		} catch (IllegalArgumentException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, null);
		}

		return j;
	}

	/**
	 * 执行查询
	 * 
	 * @return
	 */

	public static Integer select(String sql, Object... key) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet set = null;
		Integer count = 0;
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			set = ps.executeQuery(sql);
			if (set.next()) {
				count = set.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, null);
		}
		return count;
	}

	public static String selectName(String sql, Object... key) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet set = null;
		String name = null;
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			if (key.length != 0) {
				for (int i = 0; i < key.length; i++) {
					ps.setObject(i + 1, key[i]);
				}
			}
			set = ps.executeQuery();

			if (set.next()) {
				name = set.getString("uname");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, null);
		}
		return name;
	}

	/**
	 * 执行更新操作
	 * 
	 * @param
	 * 
	 * @return
	 */
	public static int update(String sql, Object... key) {
		int count = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		conn = DBUtil.getConn();
		try {
			ps = conn.prepareStatement(sql);
			for (int j = 0; j < key.length; j++) {
				ps.setObject(j + 1, key[j]);
			}
			count = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, null);
		}
		return count;
	}

	public static int deleteData(Object o) {
		// 获取反射

		Class<? extends Object> cls = o.getClass();
		// 获取创建对象的类名,就是要插入数据的表的名字
		String tempName = cls.getSimpleName();
		String className =tempName.toLowerCase();
		// 获取所有的属性
		Field[] fs = cls.getDeclaredFields();
		String sql = "delete from " + className + " where ";
		Field[] fst = new Field[fs.length];
		int count = 0;
		Object value = null;
		for (int i = 0; i < fs.length; i++) {
			Field f = fs[i];
			f.setAccessible(true);
			try {
				value = f.get(o);
				System.out.println(value);
				System.out.println("?");
			} catch (IllegalArgumentException | IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			if ("int".equals(f.getType().getName())) {
				if (0 != (int) value) {
					fst[count] = f;
					count++;
				}
			} else if ("java.lang.String".equals(f.getType().getName())) {
				if (null != (String) value) {
					fst[count] = f;
					count++;
				}
			} else if ("java.sql.Date".equals(f.getType().getName())
					|| "java.util.Date".equals(f.getType().getName())) {
				if (null != (Date) value) {
					fst[count] = f;
					count++;
				}
			} else if ("java.lang.Integer".equals(f.getType().getName())) {
				if (null != (Integer) value) {
					fst[count] = f;
					count++;
				}
			}
		}
		for (int i = 0; i < count; i++) {
			if (i < count - 1) {
				sql = sql + fst[i].getName() + " = ? and ";
			} else {
				sql = sql + fst[i].getName() + " = ? ";
			}
		}
		int temp = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		conn = DBUtil.getConn();
		if (count != 0) {
			System.out.println(sql);
			try {
				ps = conn.prepareStatement(sql);
				for (int i = 0; i < count; i++) {
					ps.setObject(i + 1, fst[i].get(o));
				}
				temp = ps.executeUpdate();
			} catch (IllegalArgumentException | IllegalAccessException | SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				DBUtil.close(conn, ps, null);
			}
		}
		return temp;
	}
}
